package com.zhangtai.modules.dao;


import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhangtai.modules.dto.FundsRecordDto;
import com.zhangtai.modules.dto.SecurityRecordDto;
import com.zhangtai.modules.dto.SecurityRecordReportDto;
import com.zhangtai.modules.entity.employee.PromotionEntity;
import com.zhangtai.modules.entity.security.SecurityRecordEntity;
import org.apache.ibatis.annotations.*;
import org.springframework.web.bind.annotation.RequestParam;

import java.util.List;
import java.util.Map;

/**
 * 
 *
 * @author chenshun
 * @email sunlightcs@gmail.com
 * @date 2020-08-17 20:26:04
 */
@Mapper
public interface SecurityRecordDao extends BaseMapper<SecurityRecordEntity> {

    @Select("<script>" +
            "Select r.*,i.name , r.create_time as operateTime from tb_security_record r " +
            "left join tb_security s on s.security_id = r.security_id " +
            "left join tb_employee_info i on i.oid = r.oid " +
                 "<where>   r.is_delete = 0 " +
                        "<if test='time!=null and time!=&apos;&apos;'>and r.operate_time &gt;= #{time} and r.operate_time &lt;=#{endTime} </if>" +
                        "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
                        "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +
                        "<if test='type!=null'>and r.type = #{type} </if>" +
                        "<if test='name!=null and name!=&apos;&apos;'>and i.name like '%${name}%' </if>"+
                        "<if test='unitName!=null and unitName!=&apos;&apos;'>and r.unit_name like '%${unitName}%' </if>" +
                        "<if test='cityName!=null and cityName!=&apos;&apos;'>and r.city_name like '%${cityName}%' </if>" +
                        "<if test='oid!=null and oid!=&apos;&apos;'>and r.oid = #{oid} </if>" +
                        "<if test='contractGoupIds!=null and contractGoupIds.size>0'> and s.contract_main_id in " +
                        " <foreach item=\"contractMainId\" index=\"index\" collection=\"contractGoupIds\" open=\"(\" separator=\",\" close=\")\">#{contractMainId}</foreach>" +
                        "</if>"+
//                        "<if test='contractGoupIds==null or contractGoupIds.size==0'> and s.contract_main_id = null  </if>" +
                        "<if test='unitId!=null'> and s.contract_main_id = #{unitId} </if>" +
                  "</where>" +
            " order by confirm_time desc" +
            "</script>")
    List<SecurityRecordDto> getRecord(@Param(value="time") String time,
                                      @Param(value="endTime") String endTime,
                                      @Param(value = "isCheck") Integer isCheck,
                                      @Param(value = "isConfirm") Integer isConfirm,
                                      @Param(value = "type") Integer type,
                                      @Param(value = "unitName") String unitName,
                                      @Param(value = "cityName") String cityName,
                                      @Param(value = "oid") String oid,
                                      @Param(value = "name") String name,
                                      @Param(value = "contractGoupIds") List<Long> contractGoupIds,
                                      @Param(value = "unitId") Long unitId,Page<SecurityRecordDto> page);


    @Select("<script>" +
            "Select r.*,i.name , r.create_time as operateTime from tb_security_record r " +
            "left join tb_security s on s.security_id = r.security_id " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>   r.is_delete = 0 " +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +
            "<if test='unitId!=null'> and s.contract_main_id = #{unitId} </if>" +
            "</where>" +
            " order by confirm_time desc" +
            "</script>")
    List<SecurityRecordDto> getRecordDelete(
                                      @Param(value = "isCheck") Integer isCheck,
                                      @Param(value = "isConfirm") Integer isConfirm,
                                      @Param(value = "unitId") Long unitId);

    @Update("UPDATE tb_security_record r " +
            "LEFT JOIN tb_employee_security s ON r.oid = s.oid " +
            "LEFT JOIN tb_security y ON s.security_id = y.security_id " +
            "SET r.is_delete = 1 " +
            "WHERE r.is_check = 0 and " +
            " y.security_id = #{securityId}")
    void deleteAllRecordBySecurityId(@Param(value="securityId") Long securityId);

    @Select("<script>Select count(*) from tb_security_record r " +
            "Left join tb_employee_security s on r.oid = s.oid " +
            "<where> r.is_delete = 0 " +
            "and r.is_confirm = 1 " +
            "and r.confirm_time &lt;= #{endTime} " +
            "and r.confirm_time &gt;= #{startTime} " +
            "<if test='type!=1'>and s.security_id  = #{securityId} </if>" +
            "<if test='type==1'>and s.funds_id  = #{securityId} </if>" +
            "</where>" +
            "</script>")
    int getConfirmRecordCount(@Param(value="startTime") String startTime,
                              @Param(value="endTime") String endTime,
                              @Param(value="securityId") String securityId,
                              @Param(value="type") Integer type);

    @Select("<script>" +
            "Select r.*,i.name ,i.idcard,i.job_system_id from tb_security_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>   r.is_delete = 0 " +
            "<if test='time!=null and time!=&apos;&apos;'>and r.operate_time &gt;= #{time} and r.operate_time &lt;=#{endTime} </if>" +
            "<if test='name!=null and name!=&apos;&apos;'>and i.name like '%${name}%' </if>"+
            "<if test='unitName!=null and unitName!=&apos;&apos;'>and s.security_name like '%${unitName}%' </if>"+

            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +
            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +
            "</where>" +
            " order by confirm_time desc" +
            "</script>")
    List<SecurityRecordReportDto> getRecordReport(@Param(value = "contractIds") List<Long> contractIds,
                                     @Param(value="time") String time,
                                      @Param(value="endTime") String endTime,
                                      @Param(value = "isConfirm") Integer isConfirm,
                                                  @Param(value = "name") String name,
                                                  @Param(value = "unitName") String unitName,
                                                  Page<SecurityRecordReportDto> page);

    @Select("<script>" +
            "Select r.*,i.name  ,i.idcard,i.job_system_id ,i.org_id,i.unit_id ,i.job_system_id,i.code   from tb_security_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +
            "<where>   r.is_delete = 0 " +
            "<if test='time!=null and time!=&apos;&apos;'>and r.operate_time &gt;= #{time} and r.operate_time &lt;=#{endTime} </if>" +
            "<if test='name!= null' > and i.name like  '%${name}%'  </if>"+
            "<if test='name == null' >  </if>"+
            "<if test='unitName!=null and unitName!=&apos;&apos;'>and r.unit_name like '%${unitName}%' </if>"+
            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +
            "</where>" +
            " order by confirm_time desc" +
            "</script>")
    List<SecurityRecordReportDto> getExportRecordReport(@Param(value="time") String time,
                                                  @Param(value="endTime") String endTime,
                                                  @Param(value = "isConfirm") Integer isConfirm,
                                                  @Param(value = "name") String name,
                                                        @Param(value = "unitName") String unitName
            ,@Param("contractIds") List<Long> contractIds
                                                        );

    @Select("<script>" +
            " select record_id from tb_security_record " +
            "<where> security_id = #{securityId}  and is_confirm = 0 and is_delete = 0" +
            "<if test = 'status ==0'> and is_check = 0 </if>" +
            "</where>" +
            "</script>")
    List<String> getAllSecurity(@Param(value="securityId")Long id,
                                @Param(value="status")Integer status);




    @Update("update  tb_security_record  set is_delete =1  where  is_confirm = 0 and is_delete = 0 and unit_id= #{unitId}")
   int deleteAllSecurity(@Param("unitId")Long unitId);

    @Select("<script>" +
            "Select r.*,i.name , r.create_time as operateTime from tb_security_record r " +
            "left join tb_security s on s.security_id = r.security_id " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>   r.is_delete = 0 " +
            " and r.create_time &gt;= #{startTime} " +
            " and r.create_time &lt;= #{endTime} " +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +
            "<if test='unitId!=null'> and s.contract_main_id = #{unitId} </if>" +
            "</where>" +
            " order by confirm_time desc" +
            "</script>")
    List<SecurityRecordDto> getSecurityRecord(
            @Param(value="startTime") String startTime,
            @Param(value="endTime") String endTime,
            @Param(value = "isCheck") Integer isCheck,
            @Param(value = "isConfirm") Integer isConfirm,
            @Param(value = "unitId") Long unitId);


}
